//
//  DBManager.m
//  DSYDouban
//
//  Created by dllo on 16/1/5.
//  Copyright © 2016年 lanou3g. All rights reserved.
//

#import "DBManager.h"
#import <sqlite3.h>

@interface DBManager ()
{
    sqlite3 *db;
}

@end

@implementation DBManager

+ (DBManager *)createDB
{
    static DBManager *manager = nil;
    static dispatch_once_t onceToken;
    dispatch_once(&onceToken, ^{
        manager = [[DBManager alloc] init];
    });
    return manager;
}

- (void)open {
    // 存放document路径
    NSString *documentPath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) firstObject];
    
    // sqlite路径
    NSString *sqlitePath = [documentPath stringByAppendingPathComponent:@"user.sqlite"];
    
    NSLog(@"%@", sqlitePath);
    
    // 打开数据库
    int result = sqlite3_open(sqlitePath.UTF8String, &db);
    
    // 判断是否打开
    if (result == SQLITE_OK) {
        NSLog(@"数据库打开成功");
    } else {
        NSLog(@"数据库打开失败");
    }
}

- (void)close {
    // 关闭数据库
    int result = sqlite3_close(db);
    if (result == SQLITE_OK) {
        NSLog(@"数据库关闭成功");
    } else {
        NSLog(@"数据库关闭失败");
    }
}

- (void) create
{
    // 数据库设计，用户ID，用户名，用户密码，用户邮箱，用户联系方式，是否处于登陆状态,默认是不在线状态(0)
    NSString *sqlStr = @"create table if not exists userInfo (userId integer primary key autoincrement, username text unique, password text, email text, phone text, status integer)";
    
    // 创建错误指针
    char *error = nil;
    // 执行创建表的
    sqlite3_exec(db, sqlStr.UTF8String, nil, nil, &error);
    
    // 判断是否出现错误
    if (error == nil) {
        NSLog(@"创建表 %@ 成功", @"userInfo");
    } else {
        NSLog(@"创建表 %@ 失败", @"userInfo");
    }
    
    NSString *activitySql = @"create table if not exists activityCollection (collectionId integer primary key autoincrement, name text, username text)";

    int result = sqlite3_exec(db, activitySql.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"创建activityCollection表成功");
    } else {
        NSLog(@"创建actitityCollection表失败");
    }
    
}



- (void)insert:(NSString *)sql {
    //
//    NSString *sqlStr = @"insert into userInfo (username, password, email, phone, status) values ('dsy', 'dsy1672', '870929851@qq.com', '15382022130', 0)";
    
    // 创建错误指针
    char *error = nil;
    
    sqlite3_exec(db, sql.UTF8String, nil, nil, &error);
    
    if (error == nil) {
        NSLog(@"插入成功");
    } else {
        NSLog(@"插入失败");
    }
    
}

- (void)update:(NSString *)sql
{
//    NSString *sqlStr = @"update userInfo set 'username' = '戴义' where userId = 2";
    // 执行sql修改命令
    char *error = nil;
    sqlite3_exec(db, sql.UTF8String, nil, nil, &error);
    if (error == nil) {
        NSLog(@"修改成功");
    } else {
        NSLog(@"修改失败");
    }
}

- (void)deleteData:(NSString *)sql
{
//    NSString *sqlStr = @"delete from userInfo where userId = 3";
    
    char *error = nil;
    int result = sqlite3_exec(db, sql.UTF8String, nil, nil, &error);
    
    if (result == SQLITE_OK) {
        NSLog(@"删除成功");
    } else {
        NSLog(@"删除失败");
    }
}

- (NSMutableArray *)select:(NSString *)sql
{
    // 测试sqlStr语句
    NSString *sqlStr = @"select * from userInfo";
    // 准备sql语句
    sqlite3_stmt *stmt = nil;
    int result;
    
    
    if (sql == nil) {
        result = sqlite3_prepare_v2(db, sqlStr.UTF8String, -1, &stmt, NULL);
    } else {
        result = sqlite3_prepare_v2(db, sql.UTF8String, -1, &stmt, NULL);
    }

    
    if (result == SQLITE_OK) {
        NSMutableArray *dataArr = [NSMutableArray array];
        
        // 单步执行语句
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            
            // 创建存储数据的字典
            NSMutableDictionary *dic = [NSMutableDictionary dictionary];
            
            // 取出用户id
            NSInteger userId = sqlite3_column_int(stmt, 0);
            
            // 取出用户名
            const unsigned char *username = sqlite3_column_text(stmt, 1);
            // 将C语言中字符串转换成oc中字符串对象
            NSString *nameString = [NSString stringWithUTF8String:(const char *)username];
            
            // 取出用户密码
            const unsigned char *password = sqlite3_column_text(stmt, 2);
            NSString *passString = [NSString stringWithUTF8String:(const char *)password];
            
            // 取出邮箱
            const unsigned char *email = sqlite3_column_text(stmt, 3);
            NSString *emailString = [NSString stringWithUTF8String:(const char *)email];
            
            // 取出联系方式
            const unsigned char *phone = sqlite3_column_text(stmt, 4);
            NSString *phoneString = [NSString stringWithUTF8String:(const char *)phone];
            
            // 取出登陆状态
            int status = sqlite3_column_int(stmt, 5);
            
            
            [dic setObject:[NSNumber numberWithInteger:userId] forKey:@"userId"];
            [dic setObject:nameString forKey:@"username"];
            [dic setObject:passString forKey:@"password"];
            [dic setObject:emailString forKey:@"email"];
            [dic setObject:phoneString forKey:@"phone"];
            [dic setObject:[NSNumber numberWithInt:status] forKey:@"status"];
            
            
            [dataArr addObject:dic];
            
        }
        sqlite3_finalize(stmt);
        
        return dataArr;
    } else {
        return nil;
    }
}



// 查看当前用户
- (NSDictionary *)currentUser {
    
    // 从本地数据库中查询正在登陆账号
    NSArray *currentLogin = [self select:@"select * from userInfo where status = 1"];
    if (currentLogin.count == 0) {
        return nil;
    } else {
        return currentLogin[0];
    }
}

// 对账户登陆状态设置
- (void)setStatus:(NSInteger)status withUserName:(NSString *)username {
    
    [self update:[NSString stringWithFormat:@"update userInfo set status = %ld where username = '%@'", status, username]];
}

// 活动收藏表的查询
- (NSMutableArray *)selectActivity:(NSString *)sql {
    sqlite3_stmt *stmt = nil;
    NSLog(@"dskjfldjs");
    
    int result = sqlite3_prepare_v2(db, sql.UTF8String, -1, &stmt, NULL);
    NSLog(@"%d", result);
    
    if (result == SQLITE_OK) {
        

        NSMutableArray *arr = [NSMutableArray array];
        
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            
            const unsigned char *name = sqlite3_column_text(stmt, 1);
            const unsigned char *username = sqlite3_column_text(stmt, 2);
            
            NSMutableDictionary *dic = [NSMutableDictionary dictionary];
            
            [dic setObject:[NSString stringWithUTF8String:(const char*)name] forKey:@"title"];
            [dic setObject:[NSString stringWithUTF8String:(const char *)username] forKey:@"username"];
        
            [arr addObject:dic];
//            printf("%s--------%s\n", name, username);
        }
        
        sqlite3_finalize(stmt);
        return arr;
        
    } else {
        return nil;
    }
}

@end
